package com.sjq.common.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.sjq.common.dao.GenericDao;
import com.sjq.common.dao.LoginLogDao;
import com.sjq.common.entity.LoginLog;
import com.sjq.common.util.PrimaryKeyUtil;
@Repository
public class LoginLogDaoImpl extends GenericDao<LoginLog> implements LoginLogDao {

	@Override
	public List<LoginLog> selectAll(LoginLog tsd) {
		// TODO Auto-generated method stub
		
		String where_tj=" where 1=1 ";
		
		if(tsd.getXXZJBH()!=null&&!"".equals(tsd.getXXZJBH())){
			where_tj+=" and xxzjbh='"+tsd.getXXZJBH()+"'";
		}
		
		if(tsd.getYHM()!=null&&!"".equals(tsd.getYHM())){
			where_tj+=" and yhm like '%"+tsd.getYHM()+"%'";
		}
		
		if(tsd.getGAJGJGDM()!=null&&!"".equals(tsd.getGAJGJGDM())){
			where_tj+=" and gajgjgdm like rtrim('"+tsd.getGAJGJGDM()+"','0')||'%'";
		}
		
		if(tsd.getDLSJ_RQSJ()!=null&&!"".equals(tsd.getDLSJ_RQSJ())){
			where_tj+=" and to_date(to_char(DLSJ_RQSJ,'yyyy-mm-dd'),'yyyy-mm-dd') >= to_date('"+tsd.getDLSJ_RQSJ().substring(0,10)+"','yyyy-mm-dd') and to_date(to_char(DLSJ_RQSJ,'yyyy-mm-dd'),'yyyy-mm-dd') <= to_date('"+tsd.getDLSJ_RQSJ().substring(11)+"','yyyy-mm-dd')";
		}
		
		if(tsd.getIPDZ()!=null&&!"".equals(tsd.getIPDZ())){
			where_tj+=" and ipdz = '"+tsd.getIPDZ()+"'";
		}
		
		String sql = "select XXZJBH,(select xm from T_SYS_JY_JYXX where yhm=t.yhm)YHM,(select dwmc from t_sys_dw_gajg where dwbm=t.GAJGJGDM)GAJGJGDM,to_char(DLSJ_RQSJ,'yyyy-mm-dd hh24:mi:ss')DLSJ_RQSJ,IPDZ from T_RZ_DL_LOG t "+where_tj+" order by DLSJ_RQSJ desc";
		List<LoginLog> list = this.getJdbcTemplate().query(sql, new RowMapper<LoginLog>(){

			@Override
			public LoginLog mapRow(ResultSet rs, int index) throws SQLException {
				// TODO Auto-generated method stub
				LoginLog tsd = new LoginLog();
				tsd.setXXZJBH(rs.getString("xxzjbh"));
				tsd.setYHM(rs.getString("yhm"));
				tsd.setGAJGJGDM(rs.getString("gajgjgdm"));
				tsd.setDLSJ_RQSJ(rs.getString("DLSJ_RQSJ"));
				tsd.setIPDZ(rs.getString("ipdz"));
				return tsd;

			}
			
			
		}); 
		
		return list;
	}

	@Override
	public List<LoginLog> selectPage(int start, int end, LoginLog tsd) {
		// TODO Auto-generated method stub
		String where_tj=" where 1=1 ";
		
		if(tsd.getXXZJBH()!=null&&!"".equals(tsd.getXXZJBH())){
			where_tj+=" and xxzjbh='"+tsd.getXXZJBH()+"'";
		}
		
		if(tsd.getYHM()!=null&&!"".equals(tsd.getYHM())){
			where_tj+=" and yhm like '%"+tsd.getYHM()+"%'";
		}
		
		if(tsd.getGAJGJGDM()!=null&&!"".equals(tsd.getGAJGJGDM())){
			where_tj+=" and gajgjgdm like rtrim('"+tsd.getGAJGJGDM()+"','0')||'%'";
		}
		
		if(tsd.getDLSJ_RQSJ()!=null&&!"".equals(tsd.getDLSJ_RQSJ())){
			where_tj+=" and to_date(to_char(DLSJ_RQSJ,'yyyy-mm-dd'),'yyyy-mm-dd') >= to_date('"+tsd.getDLSJ_RQSJ().substring(0,10)+"','yyyy-mm-dd') and to_date(to_char(DLSJ_RQSJ,'yyyy-mm-dd'),'yyyy-mm-dd') <= to_date('"+tsd.getDLSJ_RQSJ().substring(11)+"','yyyy-mm-dd')";
		}
		
		if(tsd.getIPDZ()!=null&&!"".equals(tsd.getIPDZ())){
			where_tj+=" and ipdz = '"+tsd.getIPDZ()+"'";
		}
		
		String sql = "select XXZJBH,(select xm from T_SYS_JY_JYXX where yhm=t.yhm)YHM,(select dwmc from t_sys_dw_gajg where dwbm=t.GAJGJGDM)GAJGJGDM,to_char(DLSJ_RQSJ,'yyyy-mm-dd hh24:mi:ss')DLSJ_RQSJ,IPDZ from T_RZ_DL_LOG t "+where_tj+" order by DLSJ_RQSJ desc";
		String strSql = "SELECT * FROM (SELECT ROWNUM ROW_NUM,my_table.* FROM("+sql+" ) my_table ) WHERE  ROW_NUM BETWEEN "+start+" AND "+end+"";
		//System.out.println(strSql);
		List<LoginLog> list = this.getJdbcTemplate().query(strSql, new RowMapper<LoginLog>(){
			@Override
			public LoginLog mapRow(ResultSet rs, int index) throws SQLException {
				// TODO Auto-generated method stub
				LoginLog tsd = new LoginLog();
				tsd.setXXZJBH(rs.getString("xxzjbh"));
				tsd.setYHM(rs.getString("yhm"));
				tsd.setGAJGJGDM(rs.getString("gajgjgdm"));
				tsd.setDLSJ_RQSJ(rs.getString("DLSJ_RQSJ"));
				tsd.setIPDZ(rs.getString("ipdz"));
				return tsd;
			}
			
			
		}); 
		
		return list;
		
	}

	@Override
	public int insert(LoginLog tsd) {
		String sql="insert into T_RZ_DL_LOG(XXZJBH,YHM,GAJGJGDM,IPDZ,DLSJ_RQSJ)values"
				+"('"+PrimaryKeyUtil.getUUID()+"','"+tsd.getYHM()+"','"+tsd.getGAJGJGDM()+"','"+tsd.getIPDZ()+"',sysdate)";
		int i = this.getJdbcTemplate().update(sql);
		//返回int值
	
		return i;
	}

}
